using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class SelectByType : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadGenreList();
            viewButton.Enabled = false;
        }
    }

    protected void viewButton_Click(object sender, EventArgs e)
    {
        LoadTitleInfo();
    }

    protected void selectButton_Click(object sender, EventArgs e)
    {
        if (LoadTitleList())
        {
            viewButton.Enabled = true;
        }
        else
        {
            viewButton.Enabled = false;
        }
    }
 
    private void LoadGenreList()
    {
        //Declare objects
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader reader;

        string query = @"SELECT Type AS 'GENRE' FROM Type";

        string connectionString =
            ConfigurationManager.ConnectionStrings["ArkhamBooks"].ConnectionString;

        //Initialize connection
        conn = new SqlConnection(connectionString);

        //Create command
        comm = new SqlCommand(query, conn);

        //Database code Try/Catch block
        try
        {
            //Open connection
            conn.Open();

            //Execute command
            reader = comm.ExecuteReader();

            //Populate genre list
            genreList.DataSource = reader;
            genreList.DataValueField = "GENRE";
            genreList.DataTextField = "GENRE";
            genreList.DataBind();

            //CLose reader
            reader.Close();
        }
        catch
        {
            //Display error message
            dbErrorLabel.Text = "Error loading list of genres";
        }
        finally
        {
            //Close connection
            conn.Close();
        }

        isbnTextBox.Text = "";
        pubTextBox.Text = "";
        authTextBox.Text = "";
        costTextBox.Text = "";
        sellingTextBox.Text = "";
        qtyTextBox.Text = "";
    }

    private bool LoadTitleList()
    {
        //Declare objects
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader reader;
        bool success = false;

        string query = @"SELECT Title AS 'TITLES' FROM Title WHERE Type = @genre";

        string connectionString =
            ConfigurationManager.ConnectionStrings["ArkhamBooks"].ConnectionString;

        //Initialize connection
        conn = new SqlConnection(connectionString);

        //Create command
        comm = new SqlCommand(query, conn);

        //Add parameter value
        comm.Parameters.AddWithValue("@genre", genreList.SelectedValue);

        //Database code Try/Catch block
        try
        {
            //Open connection
            conn.Open();

            //Execute command
            reader = comm.ExecuteReader();

            if (reader.HasRows)
            {
                //Populate genre list
                titleList.DataSource = reader;
                titleList.DataValueField = "TITLES";
                titleList.DataTextField = "TITLES";
                titleList.DataBind();

                //CLose reader
                reader.Close();
                success = true;
            }
            else
            {
                //Populate genre list
                titleList.Items.Clear();
                titleList.Items.Insert(0, new ListItem("No titles for this genre"));
             
                //Close the reader
                reader.Close();
            }
        }
        catch
        {
            //Display error message
            dbErrorLabel.Text = "Error loading list of genres";
        }
        finally
        {
            //Close connection
            conn.Close();
        }
        
        isbnTextBox.Text = "";
        pubTextBox.Text = "";
        authTextBox.Text = "";
        costTextBox.Text = "";
        sellingTextBox.Text = "";
        qtyTextBox.Text = "";
        return success;

    }

    private void LoadTitleInfo()
    {
        //Declare objects
        SqlConnection conn;
        SqlCommand comm;
        SqlDataReader reader;

        string query = 
            "SELECT " +
                "Publisher.Name AS 'PUBLISHER', " +
                "Title.QtyOnHand AS 'QUANTITY', " +
                "Title.Cost AS 'COST', " +
                "Title.SellingPrice AS 'SELL', " + 
                "Title.Isbn AS 'ISBN', " +
                "FirstName AS 'FIRST', " +
                "LastName AS 'LAST' " +
            "FROM " +
                "Publisher, " +
                "Title, " +
                "Author, " + 
                "Wrote " + 
            "WHERE " +
                "Title.Isbn = Wrote.Isbn " +
            "AND " +
                "Wrote.AuthorId = Author.AuthorId " +
            "AND " + 
                "Title.PubId = Publisher.PubId " + 
            "AND " +
                "Title.Title = @title";

        string connectionString =
            ConfigurationManager.ConnectionStrings["ArkhamBooks"].ConnectionString;

        //Initialize connection
        conn = new SqlConnection(connectionString);

        //Create command
        comm = new SqlCommand(query, conn);

        //Add parameter value
        comm.Parameters.AddWithValue("@title", titleList.SelectedValue);

        //Database code Try/Catch block
        try
        {
            //Open connection
            conn.Open();

            //Execute command
            reader = comm.ExecuteReader();

            if (reader.Read())
            {
                isbnTextBox.Text = reader["ISBN"].ToString();
                costTextBox.Text = reader["COST"].ToString();
                sellingTextBox.Text = reader["SELL"].ToString();
                qtyTextBox.Text = reader["QUANTITY"].ToString();
                pubTextBox.Text = reader["PUBLISHER"].ToString();
                authTextBox.Text = reader["FIRST"].ToString() + " " + reader["LAST"].ToString();
            }
            else
            {
                dbErrorLabel.Text = "Error loading book information";
            }
            //Close reader
            reader.Close();
        }
        catch
        {
            //Display error message
            dbErrorLabel.Text = "Error loading list of genres";
        }
        finally
        {
            //Close connection
            conn.Close();
        }
    }
}
